# def crontab(str):
#     echo "* * * * * hostname >> /tmp/tmp.txt" >> / var / spool / cron / root
from templates.config import conn
import threading
lock = threading.Lock()
import paramiko
output_file = 'paramiko.org'
cur = conn.cursor()
def paramiko_GKG(hostname, command):
    print('running')
    try:
        port = '22'

        # created client using paramiko
        client = paramiko.SSHClient()

        # here we are loading the system
        # host keys
        client.load_system_host_keys()
        client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        # connecting paramiko using host
        # name and password
        client.connect(hostname, port=22, username='root',
                       password='zbl@090108')

        # below line command will actually
        # execute in your remote machine
        (stdin, stdout, stderr) = client.exec_command(command)

        # redirecting all the output in cmd_output
        # variable
        cmd_output = stdout.read()
        print('log printing: ', command, cmd_output)

        # we are creating file which will read our
        # cmd_output and write it in output_file
        with open(output_file, "w+") as file:
            file.write(str(cmd_output))

        # we are returning the output
        return output_file
    finally:
        client.close()




# cur = conn.cursor()
def add_cron(host, laststr):
    # sql commands
    sql = "INSERT INTO cron(host, cmd) VALUES ('%s','%s')" %(host, laststr)
    # execute(sql)
    cur.execute(sql)
    # commit
    conn.commit()  # 对数据库内容有改变，需要commit()
    # conn.close()

def list_cron():
    # sql commands
    sql = "select * from cron"
    # execute(sql)
    cur.execute(sql)
    result = cur.fetchall()
    return result

def add_it(host,ip,type,zone,resource):
    # sql commands
    sql = "INSERT INTO servers VALUES (null,'%s','%s','%s','%s','%s')" %(host,ip,type,zone,resource)
    # execute(sql)
    lock.acquire()
    cur.execute(sql)
    lock.release()
    # commit
    conn.commit()  # 对数据库内容有改变，需要commit()

def del_servers(id):
    # sql commands
    sql = "delete from  servers where id='%s'" %(id)
    # execute(sql)
    cur.execute(sql)
    # commit
    conn.commit()  # 对数据库内容有改变，需要commit()



def get_uid(username):
    sql = "select id from user where username='%s'" % (username)
    cur.execute(sql)
    uid=cur.fetchall()[0][0]
    return uid

#hard info
def list_servers():
    # sql commands
    sql = "select * from servers"
    # execute(sql)
    cur.execute(sql)
    content = cur.fetchall()
    print(content)
    return content
def list_assets():
    # sql commands
    sql = "select * from assets"
    # execute(sql)
    cur.execute(sql)
    content2 = cur.fetchall()
    print(content2)
    return content2
def list_accounts():
    # sql commands
    sql = "select * from accounts"
    # execute(sql)
    cur.execute(sql)
    content3 = cur.fetchall()
    print(content3)
    return content3

if __name__ == '__main__':
    list_servers()
    list_assets()
    list_accounts()
    # a=paramiko_GKG('223.247.189.9','echo "* * * * * hostname >> /tmp/tmp.txt" >> /var/spool/cron/root')
    # print(a)
    # print(type(a))
    #add_it('22','myname', '2.2.2.2', 'cloud', 'beijing', '1core2g3g')